﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{

    public class DSTDNVRData : BaseFunction
    {

        byte[] b;
        string value;
        public DSTDNVRData()
            : base()
        {

        }
        public DSTDNVRData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetWBBST(DataRow dwUI)
        {
            string TableName = "DNVR";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tableLocal = null;
            string SO,MAN,CUSTOMER,EXP,USER,FROMDATE,ENDDATE;
            SO = dwUI["SO"].ToString().Trim();
            MAN = dwUI["MAN"].ToString().Trim();
            CUSTOMER = dwUI["CUSTOMER"].ToString().Trim();
            EXP = dwUI["EXP"].ToString().Trim();
            USER = dwUI["USER"].ToString().Trim();
            FROMDATE = dwUI["FROMDATE"].ToString().Trim();
            ENDDATE = dwUI["ENDDATE"].ToString().Trim();

            try
            {

                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT C.VKPKUNDNR,K.KDKUNDNA,C.VKPSACHBE,TRIM(D.HRVNAME) || TRIM(D.HRNNAME) AS NAME, "
                    + " C.VKPVG,E.VGDES,T.VRKRENR,T.VRPREPOS,T.VRKREART,T.VRKREDAT, "
                    + " TRIM(C.VKPAUFKRS)||TO_NUMBER(C.VKPAUFLFD)||'-'||TO_NUMBER(C.VKPAUFPOS) AS CO,C.VKPART,C.VKPFARBE, "
                    + " TRIM(C.VKPKDARTNR) AS CSART,C.VKPMENGE1 * 0.01 AS COQTY,C.VKPVERS * 0.01 AS DEQTY, "
                    + " C.VKPMEEH,C.VKPPREIS * 0.001 AS COPR,C.VKPPREH,C.VKPHWAEHRSL, "
                    + " ROUND(DECODE(TRIM(C.VKPMEEH), 'Y', C.VKPVERS * 0.9144, C.VKPVERS) * 0.01 * "
                    + " DECODE(TRIM(C.VKPMEEH), 'Y', C.VKPPREIS / 0.9144, C.VKPPREIS) *0.001, 2) AS COVALUE, "
                    + " T.INVQTY,T.VRPMEEH,T.INVPR,T.VRPPREH,T.VRPWAEHRSL,T.VALUE,T.SURCHARGE1,T.SURCHARGE2,T.SURCHARGE3, "
                    + " '' AS ARTBEM, '' AS SHIPDAT,T.VRPBUSIMODE,T.PAY,'' AS INVBEM, '' AS REMARK "
                    + " FROM VKPOS C,KDSTAMM K,HRSTAMM D, "
                    + " (SELECT SUBSTR(SDKEY, 3, 3) AS VG, SUBSTR(SDDATEN, 2, 20) AS VGDES "
                    + " FROM SDDAT WHERE SDSA = '0183' AND SUBSTR(SDKEY, 1, 2) = '00' AND SUBSTR(SDKEY, 24, 1) = 'V') E, "
                    + " (SELECT B.VRPAUFKRS,B.VRPAUFLFD,B.VRPAUFPOS, "
                    + " A.VRKRENR,B.VRPREPOS,A.VRKREART,A.VRKREDAT, "
                    + " B.VRPBUSIMODE,B.VRPMENGE * 0.01 AS INVQTY, B.VRPMEEH, "
                    + " B.VRPPREIS * 0.001 AS INVPR,B.VRPPREH,B.VRPWAEHRSL, "
                    + " TRIM(B.VRPZUART1) || ' ' || TO_CHAR(ROUND(B.VRPZUABWERT1 * 0.01, 2),'fm9999999990.00') AS SURCHARGE1, "
                    + " TRIM(B.VRPZUART2) || ' ' || TO_CHAR(ROUND(B.VRPZUABWERT2 * 0.01, 2),'fm9999999990.00') AS SURCHARGE2, "
                    + " TRIM(B.VRPZUART3) || ' ' || TO_CHAR(ROUND(B.VRPZUABWERT3 * 0.01, 2),'fm9999999990.00') AS SURCHARGE3, "
                    + " ROUND((B.VRPZUABWERT1 + B.VRPZUABWERT2 + B.VRPZUABWERT3) * 0.01 + "
                    + " DECODE(TRIM(B.VRPMEEH), 'Y', B.VRPMENGE * 0.9144, B.VRPMENGE) * 0.01 * "
                    + " DECODE(TRIM(B.VRPPREH), 'Y', B.VRPPREIS / 0.9144, B.VRPPREIS) *0.001, 2) AS VALUE, "
                    + " A.VRKKOMPL AS PAY "
                    + " FROM VRKOPF A,VRPOS B WHERE A.VRKMANDANT = B.VRPMANDANT AND A.VRKRENR = B.VRPRENR "
                    + " AND A.VRKFIBUKZ <> 'S' AND B.VRPSTORNO <> 'J' AND A.VRKREART = 'MA' ) T "
                    + " WHERE C.VKPMANDANT = K.KDMANDANT AND C.VKPKUNDNR = K.KDKUNDNR "
                    + " AND C.VKPMANDANT = D.HRMANDANT AND C.VKPSACHBE = D.HRBENID "
                    + " AND C.VKPVG = E.VG(+) AND C.VKPAUFKRS = T.VRPAUFKRS(+) "
                    + " AND C.VKPAUFLFD = T.VRPAUFLFD(+) AND C.VKPAUFPOS = T.VRPAUFPOS(+) "
                    + " AND C.VKPSA <> 'AJ' "
                    + " AND (TRIM(C.VKPAUFKRS)||TO_NUMBER(C.VKPAUFLFD)||'-'||TO_NUMBER(C.VKPAUFPOS) = '" + SO + "' "
                    + "  OR '" + SO + "' ='AAA' ) "
                    + " AND (trim(C.VKPSACHBE) = '" + MAN + "' OR '" + MAN + "' = 'AAA' )  "
                    + " AND (C.VKPKUNDNR = '" + CUSTOMER + "' OR '" + CUSTOMER + "' = '00000000' )  "
                    + " AND (trim(C.VKPVB) = '" + EXP + "' OR '" + EXP + "' = 'AAA' )  "
                    + " AND (trim(C.VKPVG) = '" + USER + "' OR '" + USER + "' = 'AAA' )  "
                    + " AND EXISTS (SELECT 1 FROM VVLIEF X,VVTLIEF Y "
                    + " WHERE X.VVMANDANT = Y.VVTMANDANT AND X.VVLIEFSCH = Y.VVTLIEFSCH  "
                    + " AND Y.VVTMANDANT = C.VKPMANDANT AND Y.VVTAUFKRS = C.VKPAUFKRS "
                    + " AND Y.VVTAUFLFD = C.VKPAUFLFD AND Y.VVTAUFPOS = C.VKPAUFPOS "
                    + " AND X.VVVERSDAT >= '" + FROMDATE + "'  AND X.VVVERSDAT <='" + ENDDATE + "' "
                    + " AND Y.VVTAUFTYP = 'K') "
                    + " ORDER BY VKPKUNDNR,CO,VRKRENR,VRPREPOS "
                    ;
                //orlReadCommand.Parameters.Add("ORDER", OracleType.NVarChar).Value = dwUI["ORDER"].ToString().Trim();
                //WBBBESCHICH
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = TableName;

                    byte[] b;
                    string value;
                    string sTemp = "";
                    string Column = "";
                    string INVPOS = "";
                    string ORDER = "";
                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        Column = "NAME";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        Column = "VGDES";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        Column = "CSART";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        INVPOS = dwTemp["VRKRENR"].ToString().Trim() + dwTemp["VRPREPOS"].ToString().Trim();
                        ORDER = dwTemp["VRKRENR"].ToString().Trim();
                        dwTemp["INVBEM"] = GetCommentsFromTXTIND("RES", ORDER);
                        dwTemp["REMARK"] = GetCommentsFromTXTIND("REP", INVPOS);
                        /*dwTemp["REMARK2"] = GetComments("REP", INVPOS, "20");
                        dwTemp["REMARK3"] = GetComments("REP", INVPOS, "30");
                        dwTemp["REMARK4"] = GetComments("REP", INVPOS, "40");
                        dwTemp["REMARK5"] = GetComments("REP", INVPOS, "50");
                        dwTemp["REMARK6"] = GetComments("REP", INVPOS, "60");
                        dwTemp["REMARK7"] = GetComments("REP", INVPOS, "70");
                        dwTemp["REMARK8"] = GetComments("REP", INVPOS, "80");
                        dwTemp["REMARK9"] = GetComments("REP", INVPOS, "90");
                        dwTemp["REMARK10"] = GetComments("REP", INVPOS, "100");*/
                        GetWASTAMM(dwTemp);
                        GetSHIPDAT(dwTemp);
                    }
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetWASTAMM(DataRow dwUI)
        {
            string TableName = "WASTAMM";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tableLocal = null;
            string ARTICLE;
            ARTICLE = dwUI["VKPART"].ToString();
            try
            {

                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "select TRIM(WAFBEM) AS WAFBEM from wastamm a  "
                    + " where a.waart = SUBSTR('"+ARTICLE+"',1,20) "
                    ;

                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = TableName;
                    byte[] b;
                    string value;
                    string sTemp = "";
                    string Column = "";
                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        Column = "WAFBEM";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        #endregion
                        dwUI["ARTBEM"] = value;

                    }

                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetSHIPDAT(DataRow dwUI)
        {
            string TableName = "SHIPDAT";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tableLocal = null;
            string CO;
            CO = dwUI["CO"].ToString().Trim();
            try
            {

                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = " SELECT max(x.vvversdat) AS SHIPDAT "
                    + "  FROM VVLIEF X, VVTLIEF Y    WHERE X.VVMANDANT = Y.VVTMANDANT "
                    + "  AND X.VVLIEFSCH = Y.VVTLIEFSCH  AND Y.VVTMANDANT = '00' "
                    + "  AND trim(Y.VVTAUFKRS)||to_number(Y.VVTAUFLFD)||'-'||to_number(Y.VVTAUFPOS) = '" + CO + "' "
                    + "  AND Y.VVTAUFTYP = 'K'  "
                    ;

                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = TableName;

                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        dwUI["SHIPDAT"] = dwTemp["SHIPDAT"].ToString().Trim();

                    }

                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public string GetComments(string txtitxtart, string txtisind,string item)
        {
            string KeyValue = "";
            string Comments = "";
            txtisind = txtisind + "                                                                                                                   ";
            txtisind = txtisind.Substring(0, 60);
            try
            {
                OracleCommand or1 = OracleDB.CreateCommand();
                or1.CommandText =
                     "SELECT TXTKPOS,TXTKTEXT FROM INTEX.TXTIND,INTEX.TXTKON"
                     + "  WHERE  TXTIMANDANT=TXTKMANDANT AND TXTKPIND=TXTIPIND "
                 + "AND TXTITXTART=:txtitxtart AND TXTISIND =:txtisind AND TXTKPOS = :item"
                 + " ORDER BY TXTKPOS";
                or1.Parameters.Add("txtitxtart", OracleType.NVarChar).Value = txtitxtart;
                or1.Parameters.Add("txtisind", OracleType.NVarChar).Value = txtisind;
                or1.Parameters.Add("item", OracleType.NVarChar).Value = item;
                //'PV912102            0 RD01                                  '
                dsResult = Connect2Proxy(or1);

                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = "TXTKON";

                    foreach (DataRow dwTemp in dsResult.Tables["TXTKON"].Rows)
                    {
                        Comments += GetGB2312Value(dwTemp, "TXTKTEXT");
                    }
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }

            return Comments;
        }
        public DataSet BuildData(DataRow dwUI)
        {
            string progress = "查询完成";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                EventDispatcher.Instance.RaiseStatus("查询开始", StatusEventType.StartProgress);
                setResult = GetWBBST(dwUI);
                setReturn.Merge(setResult);
                EventDispatcher.Instance.RaiseStatus("查询完成", StatusEventType.StopProgress);
            }
            catch (Exception ex)
            {
                Tracer.Error(ABPOS + ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}